<?php 
 class DormCenterDAL{

    //获取专业列表
    public function getMajorList(){
        $query="select major_id,major_name from tb_major_list;";
        $pdo=myPDO::getInstance();
        
        $row=$pdo->query($query);
        $result=$row->fetchAll(PDO::FETCH_ASSOC);
        $row->closeCursor();
        return $result;
    }

     //获取年级列表
    public function getGradeList(){
        $query="select distinct(grade) from tb_instructor_info ORDER BY grade;";
        $pdo=myPDO::getInstance();
        
        $row=$pdo->query($query);
        $result=$row->fetchAll(PDO::FETCH_ASSOC);
        $row->closeCursor();
        return $result;
    }

    //添加辅导员管理的专业和年级
    public function addInstructorMajorGrade($instructorInfo){
        $result=false;
        $query="insert into tb_instructor_info(uid,major_id,grade,add_time,status) 
        values(:uid,:major_id,:grade,now(),:status);";
        $pdo=$pdo=myPDO::getInstance();

        try{
            $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
            $pdo->beginTransaction();
            
            $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
            $statement->bindValue(':uid',$instructorInfo->uid);
            $statement->bindValue(':major_id',$instructorInfo->major);
            $statement->bindValue(':grade',$instructorInfo->grade);
            $statement->bindValue(':status',1);
            $result=$statement->execute();
            
            $pdo->commit();
        }catch(Exception $e){
            $pdo->rollBack();
            //echo "Failed: ".$e->getMessage();
        }
        
        return $result;
    }

    //删除辅导员管理的专业和年级
    public function deleteInstructorMajorGrade($instructorInfoList){
        $result=false;
        $query="update tb_instructor_info set status=:status 
        where id=:id and status=1;";
        $pdo=myPDO::getInstance();

        try{
            $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
            $pdo->beginTransaction();

            $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
            foreach($instructorInfoList as $instructorInfo){
                $statement->bindValue(':status',0);
                $statement->bindValue(':id',$instructorInfo->id);
                $result=$statement->execute();
            }

            if($result){
                $pdo->commit();
            }
        }catch(Exception $e){
            $pdo->rollBack();
            //echo "Failed: ".$e->getMessage();
        }

        return $result;
    }

    //修改辅导员管理的专业和年级
    public function editInstructorMajorGrade($instructorInfo){
        $result=false;
        $query="update tb_instructor_info set major_id=:major_id,grade=:grade where id=:id;";
        $pdo=myPDO::getInstance();
        try{
            $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
            $pdo->beginTransaction();

            $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
            $statement->bindValue(':major_id',$instructorInfo->major);
            $statement->bindValue(':grade',$instructorInfo->grade);
            $statement->bindValue(':id',$instructorInfo->id);
            $result=$statement->execute();

            $pdo->commit();
        }catch(Exception $e){
            $pdo->rollBack();
            //echo "Failed: ".$e->getMessage();
        }

        return $result;
    }

    //判断该辅导员所分配的年级和专业是否已经存在
    public function isInstructorInfo($instructorID,$grade,$major){
        $result=null;
        $query="select count(1) as count from tb_instructor_info 
        where uid=:uid and grade=:grade and major_id=:major_id and status=1;";

        $pdo=myPDO::getInstance();
        $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
        $statement->bindValue(':uid',$instructorID);
        $statement->bindValue(':grade',$grade);
        $statement->bindValue(':major_id',$major);

        if($statement->execute()){
            $result=$statement->fetch(PDO::FETCH_ASSOC);
        }
        $statement->closeCursor();
        return $result;
    }

    //获取单个辅导员的年级，专业信息列表，不需要分页
    public function getInstructorInfoList($action,$uid,$grade,$major){
        $result=null;
        //1查询全部
        $query="select id,uid,tb_major_list.major_id,major_name as major,grade 
        from tb_instructor_info,tb_major_list 
        where tb_instructor_info.uid=:uid 
        and tb_instructor_info.status=1 
        and tb_major_list.major_id=tb_instructor_info.major_id;";

        //2查询已分配，按年级查询
        $query_grade="select id,uid,tb_major_list.major_id,major_name as major,grade 
        from tb_instructor_info,tb_major_list 
        where tb_instructor_info.uid=:uid 
        and tb_instructor_info.status=1 
        and tb_instructor_info.grade=:grade 
        and tb_major_list.major_id=tb_instructor_info.major_id;";

        //3查询已分配，按专业查询
        $query_major="select id,uid,tb_major_list.major_id,major_name as major,grade 
        from tb_instructor_info,tb_major_list 
        where tb_instructor_info.uid=:uid 
        and tb_instructor_info.status=1 
        and tb_instructor_info.major_id=:major_id 
        and tb_major_list.major_id=tb_instructor_info.major_id;";

        //4查询已分配，按年级和专业查询
        $query_grade_major="select id,uid,tb_major_list.major_id,major_name as major,grade 
        from tb_instructor_info,tb_major_list 
        where uid=:uid 
        and tb_instructor_info.status=1 
        and tb_instructor_info.major_id=:major_id 
        and tb_instructor_info.grade=:grade 
        and tb_major_list.major_id=tb_instructor_info.major_id;";

        $pdo=myPDO::getInstance();
        switch($action){
            case 1:
                $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                $statement->bindValue(':uid',$uid);
                break;
            case 2:
                $statement=$pdo->prepare($query_grade,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                $statement->bindValue(':uid',$uid);
                $statement->bindValue(':grade',$grade);
                break;
            case 3:
                $statement=$pdo->prepare($query_major,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                $statement->bindValue(':uid',$uid);
                $statement->bindValue(':major_id',$major);
                break;
            case 4:
                $statement=$pdo->prepare($query_grade_major,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                $statement->bindValue(':uid',$uid);
                $statement->bindValue(':grade',$grade);
                $statement->bindValue(':major_id',$major);
                break;
            default:break;
        }

        if($statement->execute()){
            $result=$statement->fetchAll(PDO::FETCH_ASSOC);
        }
        $statement->closeCursor();

        return $result;
    }

     //获取辅导员列表
    public function getInstructorList($action,$major,$grade,$page,$pageSize,$search){
         //所有查询将只查询辅导员
         //查询全部用户将同时查找已分配和未分配用户
         //在查找所有用户状态的情况下使用专业和年级进行查询时只会查询已分配的用户
         //在未分配的情况下无法使用专业和年级来查找用户，只能已已分配和未分配来进行查询

         $result=null;

         //1 搜索全部，不带查询条件
         $query_all="select tb_account.uid,tb_account_info.name,tb_account_info.phone_number as phone from tb_account,tb_account_info 
         where tb_account.level=2 
         and tb_account.uid=tb_account_info.uid 
         and tb_account.status=1 
         order by tb_account.uid LIMIT :page,:pageSize;";

         //2 搜索全部，带查询条件（以账号或姓名进行查询）
         $query_all_search="select tb_account.uid,tb_account_info.name,tb_account_info.phone_number as phone from tb_account,tb_account_info 
         where tb_account.level=2 
         and (tb_account.uid=:search_uid or tb_account_info.name=:search_name) 
         and tb_account.uid=tb_account_info.uid 
         and tb_account.status=1 
         order by tb_account.uid LIMIT :page,:pageSize;";

         //3 查找已分配，不带查询条件
         $query_yes_all="select distinct(tb_account.uid),tb_account_info.name,tb_account_info.phone_number as phone 
         from tb_account,tb_account_info,tb_instructor_info 
         where tb_account.level=2 
         and tb_account.uid=tb_account_info.uid 
         and tb_account.uid=tb_instructor_info.uid 
         and tb_instructor_info.status=1 
         and tb_account.status=1 
         order by tb_account.uid LIMIT :page,:pageSize;";

         //4 查找已分配,带查询条件（以账号或姓名进行查询）
         $query_yes_all_search="select distinct(tb_account.uid),tb_account_info.name,tb_account_info.phone_number as phone 
         from tb_account,tb_account_info,tb_instructor_info 
         where tb_account.level=2 
         and (tb_account.uid=:search_uid or tb_account_info.name=:search_name) 
         and (tb_account.uid=tb_instructor_info.uid and tb_instructor_info.status=1) 
         and tb_account.uid=tb_account_info.uid 
         and tb_account_info.uid=tb_instructor_info.uid 
         and tb_account.status=1 
         and tb_instructor_info.status=1 
         order by tb_account.uid LIMIT :page,:pageSize;";

         //5 查询已分配，带查询条件，以专业进行查询
         $query_major="select distinct(tb_account.uid),tb_account_info.name,tb_account_info.phone_number as phone from tb_account,tb_account_info,tb_instructor_info 
         where tb_account.level=2 
         and tb_account.uid=tb_account_info.uid 
         and tb_account.uid=tb_instructor_info.uid 
         and tb_instructor_info.status=1 
         and tb_instructor_info.major_id=:major_id 
         and tb_account.status=1 
         order by tb_account.uid LIMIT :page,:pageSize;";

         //6 查询已分配，带查询条件，以专业、（账号或姓名）进行查询
         $query_major_search="select distinct(tb_account.uid),tb_account_info.name,tb_account_info.phone_number as phone from tb_account,tb_account_info,tb_instructor_info 
         where tb_account.level=2 
         and (tb_account.uid=:search_uid or tb_account_info.name=:search_name) 
         and tb_account.uid=tb_account_info.uid 
         and (tb_account.uid=tb_instructor_info.uid and tb_instructor_info.status=1)  
         and tb_instructor_info.major_id=:major_id 
         and tb_account.status=1 
         order by tb_account.uid LIMIT :page,:pageSize;";

         //7 查询已分配，带查询条件，以年级进行查询
         $query_grade="select distinct(tb_account.uid),tb_account_info.name,tb_account_info.phone_number as phone from tb_account,tb_account_info,tb_instructor_info 
         where tb_account.level=2 
         and tb_account.uid=tb_account_info.uid 
         and (tb_account.uid=tb_instructor_info.uid and tb_instructor_info.status=1) 
         and tb_instructor_info.grade=:grade 
         and tb_account.status=1 
         order by tb_account.uid LIMIT :page,:pageSize;";

         //8 查询已分配，带查询条件，以年级、（账号或姓名）进行查询
         $query_grade_search="select distinct(tb_account.uid),tb_account_info.name,tb_account_info.phone_number as phone from tb_account,tb_account_info,tb_instructor_info 
         where tb_account.level=2 
         and (tb_account.uid=:search_uid or tb_account_info.name=:search_name) 
         and tb_account.uid=tb_account_info.uid 
         and (tb_account.uid=tb_instructor_info.uid and tb_instructor_info.status=1) 
         and tb_instructor_info.grade=:grade 
         and tb_account.status=1 
         order by tb_account.uid LIMIT :page,:pageSize;";

         //9 查询已分配，带查询条件，以专业和年级进行查询
         $query_major_grade="select distinct(tb_account.uid),tb_account_info.name,tb_account_info.phone_number as phone from tb_account,tb_account_info,tb_instructor_info 
         where tb_account.level=2 
         and tb_account.uid=tb_account_info.uid 
         and (tb_account.uid=tb_instructor_info.uid and tb_instructor_info.status=1) 
         and tb_instructor_info.major_id=:major_id 
         and tb_instructor_info.grade=:grade 
         and tb_account.status=1 
         order by tb_account.uid LIMIT :page,:pageSize;";

         //10 查询已分配，带查询条件，以专业、年级、（账号或姓名）进行查询
         $query_major_grade_search="select distinct(tb_account.uid),tb_account_info.name,tb_account_info.phone_number as phone from tb_account,tb_account_info,tb_instructor_info 
         where tb_account.level=2 
         and (tb_account.uid=:search_uid or tb_account_info.name=:search_name) 
         and tb_account.uid=tb_account_info.uid 
         and (tb_account.uid=tb_instructor_info.uid and tb_instructor_info.status=1) 
         and tb_instructor_info.major_id=:major_id 
         and tb_instructor_info.grade=:grade 
         and tb_account.status=1 
         order by tb_account.uid LIMIT :page,:pageSize;";

         //11 查询未分配，不带查询条件
         $query_status_no="select distinct(tb_account.uid),tb_account_info.name,tb_account_info.phone_number as phone 
         from tb_account,tb_account_info,tb_instructor_info 
         where tb_account.level=2 
         and tb_account.uid=tb_account_info.uid 
         and tb_account.uid not in (select uid from tb_instructor_info where status=1) 
         and tb_account.status=1 
         order by tb_account.uid LIMIT :page,:pageSize;";

         //12 查询未分配，带查询条件（以账号或姓名进行查询）
         $query_status_no_search="select distinct(tb_account.uid),tb_account_info.name,tb_account_info.phone_number as phone 
         from tb_account,tb_account_info,tb_instructor_info 
         where tb_account.level=2 
         and tb_account.uid=tb_account_info.uid 
         and tb_account.uid not in (select uid from tb_instructor_info where status=1) 
         and (tb_account.uid=:search_uid or tb_account_info.name=:search_name) 
         and tb_account.status=1 
         order by tb_account.uid LIMIT :page,:pageSize;";

         $pdo=myPDO::getInstance();

         switch($action){
             //1 搜索全部，不带查询条件
             case 1:
                $statement=$pdo->prepare($query_all,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                $statement->bindValue(':page',$page,PDO::PARAM_INT);
                $statement->bindValue(':pageSize',$pageSize,PDO::PARAM_INT);
                break;
             //2 搜索全部，带查询条件（以账号或姓名进行查询）
             case 2:
                $statement=$pdo->prepare($query_all_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                $statement->bindValue(":search_uid",$search);
                $statement->bindValue(":search_name",$search);
                $statement->bindValue(':page',$page,PDO::PARAM_INT);
                $statement->bindValue(':pageSize',$pageSize,PDO::PARAM_INT);
                break;
             //3 查找已分配，不带查询条件
             case 3:
                $statement=$pdo->prepare($query_yes_all,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                $statement->bindValue(':page',$page,PDO::PARAM_INT);
                $statement->bindValue(':pageSize',$pageSize,PDO::PARAM_INT);
                break;
             //4 查找已分配,带查询条件（以账号或姓名进行查询）
             case 4:
                $statement=$pdo->prepare($query_yes_all_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                $statement->bindValue(":search_uid",$search);
                $statement->bindValue(":search_name",$search);
                $statement->bindValue(':page',$page,PDO::PARAM_INT);
                $statement->bindValue(':pageSize',$pageSize,PDO::PARAM_INT);
                break;
            //5 查询已分配，带查询条件，以专业进行查询
             case 5:
                $statement=$pdo->prepare($query_major,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                $statement->bindValue(':major_id',$major);
                $statement->bindValue(':page',$page,PDO::PARAM_INT);
                $statement->bindValue(':pageSize',$pageSize,PDO::PARAM_INT);
                break;
             //6 查询已分配，带查询条件，以专业、（账号或姓名）进行查询
             case 6:
                $statement=$pdo->prepare($query_major_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                $statement->bindValue(":search_uid",$search);
                $statement->bindValue(":search_name",$search);
                $statement->bindValue(':major_id',$major);
                $statement->bindValue(':page',$page,PDO::PARAM_INT);
                $statement->bindValue(':pageSize',$pageSize,PDO::PARAM_INT);
                break;
             //7 查询已分配，带查询条件，以年级进行查询
             case 7:
                $statement=$pdo->prepare($query_grade,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                $statement->bindValue(':grade',$grade);
                $statement->bindValue(':page',$page,PDO::PARAM_INT);
                $statement->bindValue(':pageSize',$pageSize,PDO::PARAM_INT);
                break;
             //8 查询已分配，带查询条件，以年级、（账号或姓名）进行查询
             case 8:
                $statement=$pdo->prepare($query_grade_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                $statement->bindValue(":search_uid",$search);
                $statement->bindValue(":search_name",$search);
                $statement->bindValue(':grade',$grade);
                $statement->bindValue(':page',$page,PDO::PARAM_INT);
                $statement->bindValue(':pageSize',$pageSize,PDO::PARAM_INT);
                break;
             //9 查询已分配，带查询条件，以专业和年级进行查询
             case 9:
                $statement=$pdo->prepare($query_major_grade,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                $statement->bindValue(':major_id',$major);
                $statement->bindValue(':grade',$grade);
                $statement->bindValue(':page',$page,PDO::PARAM_INT);
                $statement->bindValue(':pageSize',$pageSize,PDO::PARAM_INT);
                break;
             //10 查询已分配，带查询条件，以专业、年级、（账号或姓名）进行查询
             case 10:
                $pdo->prepare($query_major_grade_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                $statement->bindValue(":search_uid",$search);
                $statement->bindValue(":search_name",$search);
                $statement->bindValue(':major_id',$major);
                $statement->bindValue(':grade',$grade);
                $statement->bindValue(':page',$page,PDO::PARAM_INT);
                $statement->bindValue(':pageSize',$pageSize,PDO::PARAM_INT);
                break;
             //11 查询未分配，不带查询条件
             case 11:
                $statement=$pdo->prepare($query_status_no,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                $statement->bindValue(':page',$page,PDO::PARAM_INT);
                $statement->bindValue(':pageSize',$pageSize,PDO::PARAM_INT);
                break; 
             //12 查询未分配，带查询条件（以账号或姓名进行查询）
             case 12:
                $statement=$pdo->prepare($query_status_no_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                $statement->bindValue(":search_uid",$search);
                $statement->bindValue(":search_name",$search);
                $statement->bindValue(':page',$page,PDO::PARAM_INT);
                $statement->bindValue(':pageSize',$pageSize,PDO::PARAM_INT);
                break;
             default:break;
        }
         if($statement->execute()){
            $result=$statement->fetchAll(PDO::FETCH_ASSOC);
        }
        $statement->closeCursor();

        return $result;
    }

     //获取用户计数
     public function getInstructorCount($action,$grade,$major,$search){
        $result=null;

        //1 搜索全部，不带查询条件
        $query_all="select count(tb_account.uid) as count from tb_account,tb_account_info 
        where tb_account.level=2 
        and tb_account.uid=tb_account_info.uid 
        and tb_account.status=1;";

        //2 搜索全部，带查询条件（以账号或姓名进行查询）
        $query_all_search="select count(tb_account.uid) as count from tb_account,tb_account_info 
        where tb_account.level=2 
        and (tb_account.uid=:search_uid or tb_account_info.name=:search_name) 
        and tb_account.uid=tb_account_info.uid 
        and tb_account.status=1;";

        //3 查找已分配，不带查询条件
        $query_yes_all="select count(tb_account.uid) as count from tb_account,tb_account_info,tb_instructor_info 
        where tb_account.level=2 
        and (tb_account.uid=tb_instructor_info.uid and tb_instructor_info.status=1) 
        and tb_account.uid=tb_account_info.uid 
        and tb_account.status=1 
        and tb_instructor_info.status=1;";

        //4 查找已分配,带查询条件（以账号或姓名进行查询）
        $query_yes_all_search="select count(tb_account.uid) as count from tb_account,tb_account_info,tb_instructor_info 
        where tb_account.level=2 
        and (tb_account.uid=:search_uid or tb_account_info.name=:search_name) 
        and (tb_account.uid=tb_instructor_info.uid and tb_instructor_info.status=1) 
        and tb_account.uid=tb_account_info.uid 
        and tb_account.status=1 
        and tb_instructor_info.status=1;";

        //5 查询已分配，带查询条件，以专业进行查询
        $query_major="select count(tb_account.uid) as count from tb_account,tb_account_info,tb_instructor_info 
        where tb_account.level=2 
        and tb_account.uid=tb_account_info.uid 
        and (tb_account.uid=tb_instructor_info.uid and tb_instructor_info.status=1) 
        and tb_instructor_info.major_id=:major_id 
        and tb_account.status=1;";

        //6 查询已分配，带查询条件，以专业、（账号或姓名）进行查询
        $query_major_search="select count(tb_account.uid) as count from tb_account,tb_account_info,tb_instructor_info 
        where tb_account.level=2 
        and (tb_account.uid=:search_uid or tb_account_info.name=:search_name) 
        and tb_account.uid=tb_account_info.uid 
        and (tb_account.uid=tb_instructor_info.uid and tb_instructor_info.status=1)  
        and tb_instructor_info.major_id=:major_id 
        and tb_account.status=1";

        //7 查询已分配，带查询条件，以年级进行查询
        $query_grade="select count(tb_account.uid) as count from tb_account,tb_account_info,tb_instructor_info 
        where tb_account.level=2 
        and tb_account.uid=tb_account_info.uid 
        and (tb_account.uid=tb_instructor_info.uid and tb_instructor_info.status=1) 
        and tb_instructor_info.grade=:grade 
        and tb_account.status=1;";

        //8 查询已分配，带查询条件，以年级、（账号或姓名）进行查询
        $query_grade_search="select count(tb_account.uid) as count from tb_account,tb_account_info,tb_instructor_info 
        where tb_account.level=2 
        and (tb_account.uid=:search_uid or tb_account_info.name=:search_name) 
        and tb_account.uid=tb_account_info.uid 
        and (tb_account.uid=tb_instructor_info.uid and tb_instructor_info.status=1) 
        and tb_instructor_info.grade=:grade 
        and tb_account.status=1;";

        //9 查询已分配，带查询条件，以专业和年级进行查询
        $query_major_grade="select count(tb_account.uid) as count from tb_account,tb_account_info,tb_instructor_info 
        where tb_account.level=2 
        and tb_account.uid=tb_account_info.uid 
        and (tb_account.uid=tb_instructor_info.uid and tb_instructor_info.status=1) 
        and tb_instructor_info.major_id=:major_id 
        and tb_instructor_info.grade=:grade 
        and tb_account.status=1;";

        //10 查询已分配，带查询条件，以专业、年级、（账号或姓名）进行查询
        $query_major_grade_search="select count(tb_account.uid) as count from tb_account,tb_account_info,tb_instructor_info 
        where tb_account.level=2 
        and (tb_account.uid=:search_uid or tb_account_info.name=:search_name) 
        and tb_account.uid=tb_account_info.uid 
        and (tb_account.uid=tb_instructor_info.uid and tb_instructor_info.status=1) 
        and tb_instructor_info.major_id=:major_id 
        and tb_instructor_info.grade=:grade 
        and tb_account.status=1;";

        //11 查询未分配，不带查询条件
        $query_status_no="select count(tb_account.uid) as count 
        from tb_account,tb_account_info,tb_instructor_info 
        where tb_account.level=2 
        and tb_account.uid=tb_account_info.uid 
        and (tb_account.uid != tb_instructor_info.uid or (tb_account.uid != tb_instructor_info.uid and tb_instructor_info.status=0)) 
        and tb_account.status=1;";

        //12 查询未分配，带查询条件（以账号或姓名进行查询）
        $query_status_no_search="select count(tb_account.uid) as count from tb_account,tb_account_info,tb_instructor_info 
        where tb_account.level=2 
        and tb_account.uid=tb_account_info.uid 
        and (tb_account.uid != tb_instructor_info.uid or (tb_account.uid != tb_instructor_info.uid and tb_instructor_info.status=0))
        and (tb_account.uid=:search_uid or tb_account_info.name=:search_name) 
        and tb_account.status=1;";

        $pdo=myPDO::getInstance();

        switch($action){
            //1 搜索全部，不带查询条件
            case 1:
               $statement=$pdo->prepare($query_all,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
               //$statement->bindValue(':status',$status,PDO::PARAM_INT);
               break;
            //2 搜索全部，带查询条件（以账号或姓名进行查询）
            case 2:
               $statement=$pdo->prepare($query_all_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
               $statement->bindValue(":search_uid",$search);
               $statement->bindValue(":search_name",$search);
               //$statement->bindValue(':status',$status,PDO::PARAM_INT);
               break;
            //3 查找已分配，不带其他查询条件
            case 3:
               $statement=$pdo->prepare($query_yes_all,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
               $statement->bindValue(':status',$status,PDO::PARAM_INT);
               break;
            //4 查找已分配,带查询条件（以账号或姓名进行查询）
            case 4:
               $statement=$pdo->prepare($query_yes_all_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
               $statement->bindValue(":search_uid",$search);
               $statement->bindValue(":search_name",$search);
               //$statement->bindValue(':status',$status,PDO::PARAM_INT);
               break;
           //5 查询已分配，带查询条件，以专业进行查询
            case 5:
               $statement=$pdo->prepare($query_major,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
               $statement->bindValue(':major_id',$major);
               //$statement->bindValue(':status',$status,PDO::PARAM_INT);
               break;
            //6 查询已分配，带查询条件，以专业、（账号或姓名）进行查询
            case 6:
               $statement=$pdo->prepare($query_major_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
               $statement->bindValue(":search_uid",$search);
               $statement->bindValue(":search_name",$search);
               $statement->bindValue(':major_id',$major);
               //$statement->bindValue(':status',$status,PDO::PARAM_INT);
               break;
            //7 查询已分配，带查询条件，以年级进行查询
            case 7:
               $statement=$pdo->prepare($query_grade,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
               $statement->bindValue(':grade',$grade);
               //$statement->bindValue(':status',$status,PDO::PARAM_INT);
               break;
            //8 查询已分配，带查询条件，以年级、（账号或姓名）进行查询
            case 8:
               $statement=$pdo->prepare($query_grade_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
               $statement->bindValue(":search_uid",$search);
               $statement->bindValue(":search_name",$search);
               $statement->bindValue(':grade',$grade);
               //$statement->bindValue(':status',$status,PDO::PARAM_INT);
               break;
            //9 查询已分配，带查询条件，以专业和年级进行查询
            case 9:
               $statement=$pdo->prepare($query_major_grade,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
               $statement->bindValue(':major_id',$major);
               $statement->bindValue(':grade',$grade);
               //$statement->bindValue(':status',$status,PDO::PARAM_INT);
               break;
            //10 查询已分配，带查询条件，以专业、年级、（账号或姓名）进行查询
            case 10:
               $pdo->prepare($query_major_grade_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
               $statement->bindValue(":search_uid",$search);
               $statement->bindValue(":search_name",$search);
               $statement->bindValue(':major_id',$major_id);
               $statement->bindValue(':grade',$grade);
               //$statement->bindValue(':status',$status,PDO::PARAM_INT);
               break;
            //11 查询未分配，不带查询条件
            case 11:
               $statement=$pdo->prepare($query_status_no,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
               //$statement->bindValue(':status',$status-1,PDO::PARAM_INT);
               break; 
            //12 查询未分配，带查询条件（以账号或姓名进行查询）
            case 12:
               $statement=$pdo->prepare($query_status_no_search,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
               $statement->bindValue(":search_uid",$search);
               $statement->bindValue(":search_name",$search);
               //$statement->bindValue(':status',$status-1,PDO::PARAM_INT);;
               break;
            default:break;
        }
        if($statement->execute()){
            $result=$statement->fetch(PDO::FETCH_ASSOC);
         }
         $statement->closeCursor();
         
         return $result;
     }
 }
?>